package com.supermap.dataservice.cloud.china;

import com.supermap.dubbo.api.alltype.entity.AllType;
import com.supermap.dubbo.api.cloud.china.AgrStaObj;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description
 * @date 2018年12月12 下午 3:30
 * @email 1301584033@qq.com
 */
@Mapper
public interface StatisticsMpper {
    /**
     * 重庆产品统计
     * */
    @Select("select '1' as id,t3.longitude,t3.latitude,t3.areaname,t1.product_name as title,t1.make_date as creatime from\n" +
            " (select t1.product_maker,t1.product_name,t1.make_date from t_b_pms_product t1 where  t1.product_state = 6 and left(t1.make_date,10)=left(now(),10)) t1\n" +
            "inner join  t_s_base_user t2\n" +
            "on t1.product_maker=t2.ID\n" +
            "inner join t_area t3\n" +
            "on t2.areacode=t3.areacode ")
    List<AllType> getCQProduct();
    /**
     * 获取学科10大类
     * */
    @Select("select id as code,name from t_subject where del_flg=0 and id!=2")
    List<AgrStaObj> getSubject();

    /**
     * 获取农情
     */
    @Select("select year(createtime) as yeara,MONTH(createtime)as mon,COUNT(1) as nums from t_agrinfo  where del_flg=0 and  breed in \n" +
            "(select code from t_crop  where parentid in\n" +
            "(select t2.code from t_subject_crops t1\n" +
            "inner join t_crop t2\n" +
            "on t1.crop=t2.id and t1.subject=#{subjectCode})) and    areacode like concat(#{areaCode},'%') and createtime between #{beginTime} and #{endTime}  GROUP BY MONTH(createtime) ORDER BY yeara,mon asc")
    List<Map<String,Object>>  getAgrInfo(@Param("areaCode")String areaCode,@Param("beginTime")String beginTime,@Param("endTime")String endTime,@Param("subjectCode")Integer subjectCode);
    /**
     * 获取农田的经纬度
     * */
    @Select("select longitude,latitude from t_farmland t1 where id in " +
            " (select farm from t_suggest_farm where suggestId=#{suggestId})")
    List<Map<String,Object>> getFarmLonLateByAreaCode(@Param("suggestId")String suggestId);

    /**
     *
     * */
    @Select("select areacode from t_area where areacode like concat(#{areaCode},'%') and areacode !=#{areaCode}")
    List<String> getAllareaCode(@Param("areaCode")String areaCode);
    /**
     * 获取所有区县3个用户
     * */
    @Select("select longitude,latitude from t_user where  areaCode=#{areaCode} and latitude is not null and longitude is not null limit 3")
    List<Map<String,Object>> getUserLonLateByAreaCode(@Param("areaCode")String areaCode);


    /**
     * 获取所有作物小类
     * */
    @Select("select code,name from t_crop where parentid=#{cropId}")
    List<Map<String,Object>> getCropChildNode(@Param("cropId")String cropId);

    /**
     * 获取当前年的所有上传数
     * */
    @Select("  select breed,MONTH(createtime)as mon,count(id) as nums from t_agrinfo \n" +
            "  where   areacode like concat(50,'%')  and   breed in (#{cropId})  and breed is not null\n" +
            "  and YEAR(createtime)=YEAR(now()) \n" +
            "  GROUP BY MONTH(createtime) ORDER BY mon")
    List<Map<String,Object>> getYearUpdataLoadCountSum(@Param("cropId")String cropId);


    /**
     * 专家建议
     * */
    @Select("     select t1.id, t3.areaname,if(t2.longitude is null,t3.longitude,t2.longitude) as longitude,if(t2.latitude is null,t3.latitude,t2.latitude) as latitude from t_suggest t1\n" +
            "                      inner join t_user t2\n" +
            "                      on t1.userid =t2.id    and  date(t1.stime)=date(now()) and t1.crop in (${cropId})\n" +
            "                      inner join t_area t3\n" +
            "                      on t2.areacode =t3.areacode")
    List<AllType> getAllTypeToSuggestList(@Param("cropId")String cropId);

    /**
     * 专家产品
     * */
    @Select("select t2.productID as id,t1.areaname,t1.longitude,t1.latitude from t_area t1\n" +
            "inner join t_expertproduct t2\n" +
            "on t1.areacode=t2.areacode and t2.cropsid in (${cropId})  where date(t2.createTime)=date(now())  ")
    List<AllType> getAllTypeToExpertList(@Param("cropId")String cropId);

    /**
     * 公共服务产品
     * */
    @Select("            select t3.id,t5.longitude,t5.latitude,t5.areaname,t5.areaCode from  t_b_productgroup t2\n" +
            "            inner join t_b_pms_product t3\n" +
            "            on t3.product_group=t2.id and t3.product_state = 6  and\n" +
            "            date(t3.make_date)=date(now()) and find_in_set(#{cropName},t2.fit_biosname)  \n" +
            "            inner join t_s_base_user t4\n" +
            "            on t3.product_maker=t4.ID\n" +
            "            inner join t_area t5\n" +
            "            on  t4.areacode=t5.areacode ")
    List<AllType> getAllTypeToProductList2(@Param("cropName")String cropName);

    /**
     * 查看咨询
     * */
    @SelectProvider(type = StatisticsMpperSQL.class, method = "getAllTypeToAdvListSql")
    List<AllType> getAllTypeToAdvList(@Param("areaCode")String areaCode,@Param("cropId")String cropId);

    /**
     * 查看农情
     * */
    @SelectProvider(type = StatisticsMpperSQL.class, method = "getAllTypeToAgrListSql")
    List<AllType> getAllTypeToAgrList(@Param("areaCode")String areaCode,@Param("cropId")String cropId);

    /**
     * 查看灾情
     * */
    @SelectProvider(type = StatisticsMpperSQL.class, method = "getAllTypeToDisListSql")
    List<AllType> getAllTypeToDisList(@Param("areaCode")String areaCode,@Param("cropId")String cropId);
    @SelectProvider(type = StatisticsMpperSQL.class, method = "getAllTypeToDisDIPListSql")
    List<AllType> getAllTypeToDisDIPListSql(@Param("areaCode")String areaCode,@Param("cropId")String cropId);


    /**
     * 查看geo 区域
     * */
    @SelectProvider(type = StatisticsMpperSQL.class, method = "getAreaByCodeSQL")
    List<AllType>  getAreaByCode(@Param("areaCode")String areaCode);

    /**
     * 查看产品
     * */
    @SelectProvider(type = StatisticsMpperSQL.class, method = "getAllTypeToProductListSQL")
    List<AllType>  getAllTypeToProductList(@Param("areaCode")String areaCode);


    /**
     * 产品统计
     * */
    @Select("select count(t1.id) as productCountSum from t_b_pms_product  t1\n" +
            "inner join t_s_base_user t2\n" +
            "on t1.product_maker=t2.ID and t1.product_state = 6 \n" +
            "and t2.areacode like concat(#{areaCode},'%')")
    Integer getProductCountSum(@Param("areaCode")String areaCode);
    @Select("select count(productid) expertProductCountSum  from t_expertproduct where areacode like  concat(#{areaCode},'%')")
    Integer getExpertProductCountSum(@Param("areaCode")String areaCode);

    @Select("select count(t1.id)   from t_suggest t1\n" +
            "inner join t_user  t2\n" +
            "on t1.userid=t2.id and t2.areacode like concat(#{areaCode},'%')")
    Integer getExpertProductCountSum1(@Param("areaCode")String areaCode);

    @SelectProvider(type = StatisticsMpperSQL.class, method = "getFarmALLCounSumSQL")
    List<Map<String,Object>>  getFarmALLCounSum(@Param("areaCode")String areaCode);

    /**
     * 服务对象
     * */
    @SelectProvider(type = StatisticsMpperSQL.class, method = "getSerObjListByAreacodeSQL")
    List<Map<String,Object>>  getSerObjListByAreacode(@Param("areaCode")String areaCode,@Param("month")String month,@Param("cropId")String cropId);

    class StatisticsMpperSQL{
        /**
         * 咨询
         * */
        public String getAllTypeToAdvListSql(Map<String, Object> map) {
            String areaCode = (String)map.get("areaCode");
            String cropId =(String)map.get("cropId");
            StringBuilder sql = new StringBuilder();
            sql.append("select t1.id,t1.lon as longitude,t1.lat as latitude,t2.areaname from  t_advisoryinfo t1" +
                    " INNER JOIN t_area t2 on t1.areacode=t2.areacode where date(t1.uploadTime)=date(now()) ");

            if(areaCode!=null){
                if(!areaCode.equals("1") ){
                    sql.append(" and t1.areacode like '"+areaCode+"%'");
                }
            }
            if(cropId!=null){
                sql.append(" and   t1.breed in ("+cropId+") ");
            }
            return sql.toString();
        }

        /**
         * 农情
         * */
        public String getAllTypeToAgrListSql(Map<String, Object> map) {
            String areaCode = (String)map.get("areaCode");
            String cropId =(String)map.get("cropId");

            //创建stringbuilder对象
            StringBuilder sql = new StringBuilder();
            sql.append("select t1.id,t1.`longitude`,t1.`latitude`,t2.areaname from  t_agrinfo t1 " +
                    "INNER JOIN t_area t2 on t1.areacode=t2.areacode where date(t1.createtime)=date(now()) ");
            if(areaCode!=null){
                if(!areaCode.equals("1") ){
                    sql.append(" and t1.areacode like '"+areaCode+"%'");
                }
            }
            if(cropId!=null){
                sql.append(" and   t1.breed in ("+cropId+") ");
            }
            return sql.toString();
        }

        /**
         * 灾情
         * */
        public String getAllTypeToDisListSql(Map<String, Object> map) {
            String areaCode = (String)map.get("areaCode");
            String cropId =(String)map.get("cropId");
            StringBuilder sql = new StringBuilder();
            sql.append("select t1.id,t1.`longitude` ,t1.`latitude` ,t2.areaname  from" +
                    "  t_disaster t1 INNER JOIN t_area t2 on t1.areacode=t2.areacode where  date(t1.createtime)=date(now()) ");
            if(areaCode!=null){
                if(!areaCode.equals("1") ){
                    sql.append(" and t1.areacode like '"+areaCode+"%'");
                }
            }
            if(cropId!=null){
                sql.append(" and   t1.breed in ("+cropId+") ");
            }
            return sql.toString();
        }

        public String getAllTypeToDisDIPListSql(Map<String, Object> map) {
            String areaCode = (String)map.get("areaCode");
            String cropId =(String)map.get("cropId");
            StringBuilder sql = new StringBuilder();
            sql.append("select t1.id,t1.`longitude` ,t1.`latitude` ,t2.areaname  from" +
                    "  t_disaster_dip t1 INNER JOIN t_area t2 on t1.areacode=t2.areacode where  date(t1.createtime)=date(now()) ");
            if(areaCode!=null){
                if(!areaCode.equals("1") ){
                    sql.append(" and t1.areacode like '"+areaCode+"%'");
                }
            }
            if(cropId!=null){
                sql.append(" and   t1.breed in ("+cropId+") ");
            }
            return sql.toString();
        }

        /**
         * gro 区域
         * */
        public String getAreaByCodeSQL(Map<String, Object> map){
            String areaCode = (String)map.get("areaCode");
            StringBuilder sql = new StringBuilder();
            sql.append("select areacode as id,areaname,longitude,latitude from" +
                    " t_area where   length(areacode)<=4 ");
            if(areaCode!=null){
                if(!areaCode.equals("1")){
                    sql.append(" and areacode like concat("+areaCode+",'%')");
                }else{
                    sql.append(" and length(areacode)=2 ");
                }
            }

            return sql.toString();
        }
        /**
         *公共服务产品
         * */
        public String getAllTypeToProductListSQL(Map<String, Object> map){
            String areaCode = (String)map.get("areaCode");
            StringBuilder sql = new StringBuilder();
            sql.append(" select if(t3.lon is null,(select lon from t_area where areacode=left(t3.areacode, 2)),t3.lon) as longitude,if(t3.lat is null,(select lat from t_area where areacode=left(t3.areacode, 2)),t3.lat) as latitude,t3.areaname from t_b_pms_product t1 " +
                    "  inner join t_b_productgroup t2  " +
                    " on t1.product_group= t2.id and t1.product_state = 6  and  date(t1.make_date)=date(now()) ");
            if(!areaCode.equals("1") ){
                sql.append(" and t2.fit_areaid like concat("+areaCode+",'%')  ");
            }
            sql.append(" Left join t_area t3 " +
                    " on  find_in_set(t3.areacode,t2.fit_areaid)  ");
            return sql.toString();
        }

        /***
         * 注册农田
         * */

        public String getFarmALLCounSumSQL(Map<String, Object> map){
            String areaCode = (String)map.get("areaCode");
            StringBuilder sql = new StringBuilder();
            if(!areaCode.equals("1") ){

                if(areaCode.length()>2){
                    sql.append("                    select tt1.subjectName,count(tt2.farmId) as countSum from \n" +
                            "                       ( select t1.code subjectCode,t1.name as subjectName ,t3.code as cropCode,t3.name as cropName from \n" +
                            "                       ( select id,code,name from t_subject where del_flg=0 and code!=\"QX\") t1\n" +
                            "                        inner join t_subject_crops t2\n" +
                            "                        on t1.id=t2.subject\n" +
                            "                        inner join  t_crop t3\n" +
                            "                        on t2.crop=t3.id) tt1\n" +
                            "                        inner join (select t4.parentid cropCode,t1.id as farmId from t_farmland t1\n" +
                            "                          JOIN t_area t2\n" +
                            "                        ON t2.areacode = t1.code  and t2.areacode  like concat("+areaCode    +",'%')\n" +
                            "                        inner join  t_farmcrops t3 " +
                            "                        on t1.id=t3.farmland " +
                            " inner join t_crop t4 " +
                            " on t3.crop=t4.code and t4.parentid!=0) tt2 " +
                            " on tt1.cropCode=tt2.cropCode " +
                            " group by tt1.subjectCode");

                }else{
                    sql.append("select t2.areaname,count(t1.id) as countSum from t_farmland t1\n" +
                            "  JOIN t_area t2\n" +
                            "ON t2.areacode = t1.code  and t2.areacode  like concat("+areaCode+",'%')\n" +
                            "group by t2.areacode");
                }
            }else{
                sql=new StringBuilder();
                sql.append(" select t2.areaname,count(t1.id) as countSum from t_farmland t1\n" +
                        " inner JOIN t_area t2\n" +
                        "ON  left(t1.code,2) =  t2.areacode\n" +
                        "where length(t2.areacode) = 2\n" +
                        "group by t2.areaid ");
            }
            return sql.toString();
        }

        /***
         * 服务对象
         * */
        public String getSerObjListByAreacodeSQL(Map<String, Object> map){
            String areaCode = (String)map.get("areaCode");
            String  month = (String)map.get("month");
            String  cropId = (String)map.get("cropId");
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            //获取当前月第一天：
            Calendar c = Calendar.getInstance();
            c.add(Calendar.MONTH, 0);
            c.set(Calendar.DAY_OF_MONTH,1);//设置为1号,当前日期既为本月第一天
            String first = format.format(c.getTime());
            //获取当前月最后一天
            Calendar ca = Calendar.getInstance();
            ca.set(Calendar.DAY_OF_MONTH, ca.getActualMaximum(Calendar.DAY_OF_MONTH));
            String last = format.format(ca.getTime())+" 23:59:59";

            StringBuilder sql = new StringBuilder();
            sql.append(" select t1.name,count(t3.id) as value from t_role t1\n" +
                    "left join t_user_role t2 \n" +
                    "on t1.id=t2.roleid left join ( select  distinct tt1.id from t_user tt1 " +
                    "Left join  t_farmland  tt2 " +
                    "on tt1.id=tt2.userId  " +
                    "Left join t_farmcrops tt3 " +
                    "on tt2.id=tt3.farmland " +
                    "Left join t_crop tt4 " +
                    "on tt3.crop=tt4.code " +
                    "where 1=1 ");

            if(areaCode!=null){
                if(!areaCode.equals("1")){
                    sql.append(" and  tt1.areaCode like concat("+areaCode+",'%') ");
                }
            }

            if(month!=null){
                sql.append(" and tt1.createtime between '"+first+"'  and  '"+last+"' ");
            }
            if(cropId!=null){
                sql.append(" and tt4.code in ("+cropId+") ");
            }
            sql.append(" )t3 " +
                    "on t2.userid =t3.id " +
                    "where t2.roleid not in (5,7) group by t1.id ");
            return  sql.toString();
        }
    }

    /**
     * 获取专家产品
     * @return
     */
    @Select("select t2.productID as id,t2.title,t1.areaname,t1.longitude,t1.latitude, DATE_FORMAT(t2.createtime,'%Y-%m-%d %H:%i:%S') as creatime from t_area t1 \n" +
            "inner join t_expertproduct t2 on t1.areacode=t2.areacode\n" +
            "where date(t2.createtime)=date(now())")
    public List<AllType> getExpretProductByAreaCode();




}
